# coding=utf8
import sqlite3

def create_table(filename):
        conn = sqlite3.connect(filename)
        conn.execute("""create table if not exists character
                     (
                          pinyin CHAR(20) NOT NULL,
                          code INT NOT NULL,
                          frequency INT DEFAULT 0
                      )""")
        conn.commit()
        return conn


def insert_to(conn, pinyin, charcode):
    conn.execute('insert into character (pinyin, code) values(?, ?)',
                 (pinyin, charcode))
    conn.commit()


def close_db(conn):
    conn.commit()
    conn.close()


def update_frequency(conn, charcode, frequency):
    conn.execute('update character set frequency = ? where code = ?',
                 (frequency, charcode))
    conn.commit()


def main():
    conn = create_table("pinyin.db")
    with open("pinyin.data") as fh:
        for line in fh:
            dt = line.split(" ")
            c = int(dt[0], 16)
            pinyins = dt[1].replace("u:", "v").replace("(", "").replace(")", "").replace("^", "").split(",")
            for pinyin in pinyins:
                insert_to(conn, c, pinyin)
    with open('3500hanzi.data') as fh:
        for line in fh:
            line.replace(" ", "")
            line = unicode(line, "utf-8")
            for l in line:
                c = ord(l)
                update_frequency(conn, c, 10)
    close_db(conn)

if __name__ == '__main__':
    main()



